package pe.gob.osinergmin.interfase_siaf.dac.dao.impl;

import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import pe.gob.osinergmin.interfase_siaf.dac.dao.FuenteFinancDao;
import pe.gob.osinergmin.interfase_siaf.dac.domain.CtbConfigRbXCgXTfinanc;
import pe.gob.osinergmin.interfase_siaf.dac.domain.FuenteFinanc;

@Repository("fuenteFinancDao")
public class FuenteFinancDaoImpl implements FuenteFinancDao {
	
    private final Log logger = LogFactory.getLog(getClass());
	
	
	private NamedParameterJdbcTemplate jdbcParameterTemplateData;	
	private JdbcTemplate jdbcTemplateData;	
	
	
	
	@Autowired
	public void SetDataSource(DataSource dataSourceData){
		this.jdbcTemplateData = new JdbcTemplate(dataSourceData);
		this.jdbcParameterTemplateData = new NamedParameterJdbcTemplate(dataSourceData);
		
	}
	
	
	/**DECLARACION DE SQL**/
	/*private static final String SQL_SELECT_FUENTE ="SELECT distinct(fuente_fin), nombre FROM fuente_financ";
	private static final String SQL_SELECT_FUENTE_PARAMETER ="SELECT distinct(fuente_fin), nombre FROM fuente_financ WHERE fuente_fin =:fuenteFin";
	private static final String SQL_SELECT_FUENTE_PARAMETER1 ="SELECT distinct(fuente_fin), nombre FROM fuente_financ WHERE nombre =:nombre";
	*/
	private static final String SQL_SELECT_FUENTE ="SELECT fuente_fin, nombre,ano_eje FROM fuente_financ";
	private static final String SQL_SELECT_FUENTE_PARAMETER ="SELECT fuente_fin, nombre ,ano_eje FROM fuente_financ WHERE fuente_fin =:fuenteFin";
	private static final String SQL_SELECT_FUENTE_PARAMETER1 ="SELECT fuente_fin, nombre ,ano_eje FROM fuente_financ WHERE ano_eje =:anoEje";
	private static final String SQL_SELECT_FUENTE_PARAMETER2 ="SELECT fuente_fin, nombre ,ano_eje FROM fuente_financ WHERE ano_eje =:anoEje AND fuente_fin =:fuenteFin";
	
	private static final String SQL_SELECT_TIPO_FIN ="SELECT COUNT(tipo_finan) FROM ctb_config_rb_x_cg_x_tfinanc WHERE ano_ctb =:anoCtb"
			+ " AND fuente_fin=:fuenteFin AND tipo_finan=:tipoFinan";
	
	private static final String SQL_SELECT_FUENTE_PARAMETER3 ="SELECT COUNT(fuente_fin) FROM fuente_financ WHERE fuente_fin =:fuenteFin";

	@Override
	public List<FuenteFinanc> listarFuenteFinanc() {
		logger.info("Ingresando a listar fuente financ"); 
		RowMapper<FuenteFinanc> lista = ParameterizedBeanPropertyRowMapper.newInstance(FuenteFinanc.class);
		return this.jdbcTemplateData.query(SQL_SELECT_FUENTE, lista);
	}
	@Override
	public List<FuenteFinanc> listarFuenteFinancParameter(FuenteFinanc fuente) {
		logger.info("Ingresando a listar fuente finan parameters"); 
		SqlParameterSource parameters = new BeanPropertySqlParameterSource(fuente);
		RowMapper<FuenteFinanc> lista = ParameterizedBeanPropertyRowMapper.newInstance(FuenteFinanc.class);
		if(StringUtils.isNoneBlank(fuente.getAnoEje()) && StringUtils.isNoneBlank(fuente.getFuenteFin())){ 
			return this.jdbcParameterTemplateData.query(SQL_SELECT_FUENTE_PARAMETER2, parameters, lista);	
		}else if(StringUtils.isNoneBlank(fuente.getAnoEje())){
			return this.jdbcParameterTemplateData.query(SQL_SELECT_FUENTE_PARAMETER1, parameters, lista);	
		}else if(StringUtils.isNoneBlank(fuente.getFuenteFin())){
			return this.jdbcParameterTemplateData.query(SQL_SELECT_FUENTE_PARAMETER, parameters, lista);	
		}else{
			return null;
		}					
	}
	
	/*@Override
	public List<FuenteFinanc> verificarFteFin(FuenteFinanc f) throws Exception {		 
		SqlParameterSource parameters = new BeanPropertySqlParameterSource(f);
		RowMapper<FuenteFinanc> lista = ParameterizedBeanPropertyRowMapper.newInstance(FuenteFinanc.class);		
	    return this.jdbcParameterTemplateData.query(SQL_SELECT_FUENTE_PARAMETER, parameters, lista);	
	}*/
	
	@Override
	public int verificarFteFin(FuenteFinanc f) throws Exception {		 
		SqlParameterSource parameters = new BeanPropertySqlParameterSource(f);		
	    return this.jdbcParameterTemplateData.queryForInt(SQL_SELECT_FUENTE_PARAMETER3, parameters);	
	}
	
	/*@Override
	public List<CtbConfigRbXCgXTfinanc> verificarTipoFin(CtbConfigRbXCgXTfinanc f) throws Exception {		 
		SqlParameterSource parameters = new BeanPropertySqlParameterSource(f);
		RowMapper<CtbConfigRbXCgXTfinanc> lista = ParameterizedBeanPropertyRowMapper.newInstance(CtbConfigRbXCgXTfinanc.class);		
	    return this.jdbcParameterTemplateData.query(SQL_SELECT_TIPO_FIN, parameters, lista);	
	}*/
	
	@Override
	public int verificarTipoFin(CtbConfigRbXCgXTfinanc f) throws Exception {		 
		SqlParameterSource parameters = new BeanPropertySqlParameterSource(f);		
	    return this.jdbcParameterTemplateData.queryForInt(SQL_SELECT_TIPO_FIN, parameters);	
	}


}
